package fjorm;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author mladen
 */
public class StandardDao<T extends Object> extends Dao<T> {
  Map<String, Class> nonIdFieldsToType = new LinkedHashMap<String, Class>();
  Map<String, Class> allFieldsToType = new LinkedHashMap<String, Class>();
  Map<String, Class> compositeKeyFieldsToType = new LinkedHashMap<String, Class>();
  String idFieldName;
  Class idFieldType;
  String tableName;
  String nonIdFieldsCommaSeparated;
  String nonIdFieldsWithTablePrefixCommaSeparated;
  String allFieldsCommaSeparated;
  String allFieldsWithTablePrefixCommaSeparated;
  String questionInsteadOfAllFields;
  String questionInsteadOfNonIdFields;
  Class<T> tclass;
  DaoProperties daoProperties;
  boolean autoGeneratedId = false;

  public StandardDao(Class tclass, DaoProperties daoProperties) {
    this.tclass = tclass;
    TableName tableNameAnnotation = (TableName) tclass.getAnnotation(TableName.class);
    if (tableNameAnnotation == null) {
      throw new RuntimeException("Class " + tclass.getCanonicalName() + " doesn't have its TableName annotated. Please fix.");
    }
    tableName = tableNameAnnotation.table();
    this.daoProperties = daoProperties;

    StringBuilder nonIdFieldsToFetchBuilder = new StringBuilder();
    StringBuilder nonIdFieldsWithTablePrefixCommaSeparatedBuilder = new StringBuilder();
    StringBuilder questionInsteadOfNonIdFieldsBuilder = new StringBuilder();
    boolean isFirstField = true;
    for (Field field : tclass.getFields()) {
      //skip transient fields
      Transient transientAnnotation = (Transient) field.getAnnotation(Transient.class);
      if (transientAnnotation != null) {
        continue;
      }
      
      Id id = (Id) field.getAnnotation(Id.class);
      if (id != null) {
        idFieldName = field.getName();
        idFieldType = field.getType();
        if (field.getAnnotation(AutoGenerated.class) != null) {
          autoGeneratedId = true;
        }
      } else {
        if (!isFirstField) {
          nonIdFieldsToFetchBuilder.append(", ");
          nonIdFieldsWithTablePrefixCommaSeparatedBuilder.append(", ");
          questionInsteadOfNonIdFieldsBuilder.append(", ");
        } else {
          isFirstField = false;
        }
        nonIdFieldsToType.put(field.getName(), field.getType());
        nonIdFieldsToFetchBuilder.append(field.getName());
        nonIdFieldsWithTablePrefixCommaSeparatedBuilder.append(tableName).append('.').append(field.getName());
        questionInsteadOfNonIdFieldsBuilder.append('?');
      }
      allFieldsToType.put(field.getName(), field.getType());
    }
    nonIdFieldsCommaSeparated = nonIdFieldsToFetchBuilder.toString();
    nonIdFieldsWithTablePrefixCommaSeparated = nonIdFieldsWithTablePrefixCommaSeparatedBuilder.toString();
    questionInsteadOfNonIdFields = questionInsteadOfNonIdFieldsBuilder.toString();

    updateAllFieldsCommaSeparated();
  }

  private void updateAllFieldsCommaSeparated() {
    StringBuilder allFieldsCommaSeparatedBuilder = new StringBuilder();
    StringBuilder allFieldsWithTablePrefixCommaSeparatedBuilder = new StringBuilder();
    StringBuilder questionInsteadOfAllFieldsBuilder = new StringBuilder();
    if (idFieldName != null) {
      allFieldsCommaSeparatedBuilder.append(idFieldName);
      allFieldsWithTablePrefixCommaSeparatedBuilder.append(tableName).append('.').append(idFieldName);
      questionInsteadOfAllFieldsBuilder.append('?');
      if (nonIdFieldsCommaSeparated.length() > 0) {
        allFieldsCommaSeparatedBuilder.append(", ");
        allFieldsWithTablePrefixCommaSeparatedBuilder.append(", ");
        questionInsteadOfAllFieldsBuilder.append(", ");
      }
    }
    if (nonIdFieldsCommaSeparated.length() > 0) {
      allFieldsCommaSeparatedBuilder.append(nonIdFieldsCommaSeparated);
      allFieldsWithTablePrefixCommaSeparatedBuilder.append(nonIdFieldsWithTablePrefixCommaSeparated);
      questionInsteadOfAllFieldsBuilder.append(questionInsteadOfNonIdFields);
    }
    allFieldsCommaSeparated = allFieldsCommaSeparatedBuilder.toString();
    allFieldsWithTablePrefixCommaSeparated = allFieldsWithTablePrefixCommaSeparatedBuilder.toString();
    questionInsteadOfAllFields = questionInsteadOfAllFieldsBuilder.toString();
  }
  
  public static void setObjValueFromResultSet(Field field, Object obj, ResultSet rs, int i, Class fieldType) {
    try {
      if (fieldType.equals(Integer.class) || fieldType.equals(int.class)) {
        field.set(obj, rs.getInt(i++));
      } else if (fieldType.equals(String.class)) {
        field.set(obj, rs.getString(i++));
      } else if (fieldType.equals(Double.class) || fieldType.equals(double.class)) {
        field.set(obj, rs.getDouble(i++));
      } else if (fieldType.equals(Float.class) || fieldType.equals(float.class)) {
        field.set(obj, rs.getFloat(i++));
      } else if (fieldType.equals(Date.class)) {
        field.set(obj, rs.getDate(i++));
      } else if (fieldType.equals(java.util.Date.class)) {
        field.set(obj, rs.getDate(i++));
      } else if (fieldType.equals(java.sql.Timestamp.class)) {
        field.set(obj, rs.getTimestamp(i++));
      } else {
        throw new UnsupportedOperationException("Not implemented yet for " + fieldType.getCanonicalName());
      }
    } catch (SQLException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalArgumentException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SecurityException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  public Object getIdFromElem(T elem) {
    Object result = null;
    if (idFieldName != null) {
      Field idField;
      try {
        idField = tclass.getField(idFieldName);
        result = idField.get(elem);
      } catch (NoSuchFieldException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (IllegalArgumentException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (IllegalAccessException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SecurityException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
    return result;
  }
  
  public T readElementFromResultSet(ResultSet rs) throws SQLException {
      try {
        T obj = tclass.getConstructor().newInstance();
        int i = 1;
        for (Map.Entry<String, Class> nonIdField : allFieldsToType.entrySet()) {
          Field field = tclass.getField(nonIdField.getKey());
          setObjValueFromResultSet(field, obj, rs, i++, nonIdField.getValue());
        }
        return obj;
      } catch (NoSuchFieldException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (InstantiationException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (IllegalAccessException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (IllegalArgumentException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (InvocationTargetException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (NoSuchMethodException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SecurityException ex) {
        Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
      }
      return null;
  }

  public List<T> readElementsFromResultSet(ResultSet rs) throws SQLException {
    List<T> result = new ArrayList<T>();
    while (rs.next()) {
      T oneElem = readElementFromResultSet(rs);
      if (oneElem != null) {
        result.add(oneElem);
      }
    }
    return result;
  }

  public StringBuilder getQueryBeforeWhere() {
    StringBuilder query = new StringBuilder();
    query.append("select ");
    // to support inner join this was changed
    //old code = query.append(allFieldsCommaSeparated);
    query.append(allFieldsWithTablePrefixCommaSeparated);
    query.append(" from ").append(tableName);
    return query;
  }
  
  private static void addWhereFieldToQuery(StringBuilder query, String where) {
    if (where != null && where.length() > 0) {
      String trimmedLower = where.trim().toLowerCase();
      if (trimmedLower.length() > 0 && !trimmedLower.startsWith("where") && !trimmedLower.startsWith("order") && !trimmedLower.startsWith("limit ") 
              && !trimmedLower.startsWith("inner ") &&!trimmedLower.startsWith("outer ") && !trimmedLower.startsWith("join ")) {
        where = "where " + where;
      }
      query.append(" ").append(where);
    }
  }
  
  private void addParamsToQuery(PreparedStatement ps, Object... params) throws SQLException {
    if (params != null && params.length > 0) {
      int i = 1;
      for (Object obj : params) {
        if (obj instanceof Integer) {
          Integer objInt = (Integer) obj;
          ps.setInt(i++, objInt.intValue());
        } else if (obj instanceof String) {
          ps.setString(i++, String.valueOf(obj));
        } else if (obj instanceof Double) {
          ps.setDouble(i++, ((Double) obj).doubleValue());
        } else if (obj instanceof Float) {
          ps.setDouble(i++, ((Float) obj).floatValue());
        } else if (obj instanceof Date) {
          ps.setDate(i++, (Date) obj);
        } else if (obj instanceof Timestamp) {
          ps.setTimestamp(i++, (Timestamp) obj);
        } else if (obj instanceof java.util.Date) {
          Date date = new Date(((java.util.Date) obj).getTime());
          ps.setDate(i++, date);
        } else {
          Logger.getLogger(this.getClass().getName()).log(Level.WARNING, "error while assinging obj:{0} to PreparedStatement at position", String.valueOf(obj));
        }
      }
    }
  }
  

  @Override
  public List<T> read(Connection conn, String where, Object... params) throws SQLException {
    StringBuilder query = getQueryBeforeWhere();
    addWhereFieldToQuery(query, where);
    PreparedStatement ps = conn.prepareStatement(query.toString());
    List<T> result = null;
    try {
      //Logger.getLogger(this.getClass().getName()).info("Read query = " + ps.toString());
      addParamsToQuery(ps, params);
      ResultSet rs = ps.executeQuery();
      result = readElementsFromResultSet(rs);
    } finally {
      close(ps);
    }
    return result;
  }

  @Override
  public List<T> readAll(Connection conn) throws SQLException {
    return read(conn, "");
  }

  @Override 
  public T readByKey(Connection conn, Object key) throws SQLException {
    List<T> candidates = null;
    StringBuilder query = getQueryBeforeWhere();
    query.append(" where ").append(idFieldName).append(" = ?");
    PreparedStatement stmt = conn.prepareStatement(query.toString());
    try {
      //Logger.getLogger(this.getClass().getName()).info("Read by key query = " + query.toString());
      if ((idFieldType.equals(Integer.class) || idFieldType.equals(int.class)) && (key instanceof Integer)) {
        //Logger.getLogger(this.getClass().getName()).info("Setting questionmark to = " + key.toString());
        stmt.setInt(1, ((Integer) key).intValue());
      } else if (idFieldType.equals(String.class)) {
        //Logger.getLogger(this.getClass().getName()).info("Setting questionmark to = " + key.toString());
        stmt.setString(1, key.toString());
      } else {
        //Logger.getLogger(this.getClass().getName()).info("Unrecognized id type = " + String.valueOf(key));
      }
      ResultSet rs = stmt.executeQuery();
      candidates = readElementsFromResultSet(rs);
    } finally {
      close(stmt);
    }
    if (candidates == null || candidates.isEmpty()) {
      return null;
    }
    
    return candidates.get(0);
  }


  String queryForCreate = null;
  private synchronized String getQueryForCreate() {
    if (queryForCreate != null) {
      return queryForCreate;
    }
    // when having autoGenerated Id
    // insert into tablename(all fields comma separated apart from id) values (?, ?, ?, ?);
    StringBuilder query = new StringBuilder();
    query.append("insert into ").append(tableName).append("(");
    // if id key is not autogenerated add idFieldName into PreparedStatement
    if (idFieldName != null && !idFieldName.equals("") && !autoGeneratedId) {
      query.append(idFieldName);
      if (nonIdFieldsCommaSeparated.length() > 0) {
        query.append(", ");
      }
    }
    query.append(nonIdFieldsCommaSeparated).append(") values (");
    // if id key is not autogenerated add idFieldName into PreparedStatement
    if (idFieldName != null && !idFieldName.equals("") && !autoGeneratedId) {
      query.append("?");
      if (nonIdFieldsCommaSeparated.length() > 0) {
        query.append(", ");
      }
    }
    query.append(questionInsteadOfNonIdFields).append(")");
    return query.toString();
  }

  @Override
  public T create(Connection conn, T entity) throws SQLException {
    String query = getQueryForCreate();
    //Logger.getLogger(this.getClass().getName()).log(Level.INFO, "Query={0}", query);

    PreparedStatement ps = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);
    try {
      int i = 1;
      if (idFieldName != null && !idFieldName.equals("") && !autoGeneratedId) {
        setPreparedStatementFromEntityField(idFieldName, idFieldType, entity, ps, i++);
      }
      for (Map.Entry<String, Class> fieldToType : nonIdFieldsToType.entrySet()) {
        setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
      }
      //Logger.getLogger(this.getClass().getName()).log(Level.INFO, "ps={0}", ps.toString());
      ps.execute();
      // attach generated key to the object if possible
      if (idFieldName != null && idFieldName.length() > 0) {
        //Logger.getLogger(this.getClass().getName()).info("attach generated key to the object if possible");
        ResultSet keys = ps.getGeneratedKeys();
        if (keys.next()) {
          try {
            //Logger.getLogger(this.getClass().getName()).info("found generated key to the object");
            Field idField = tclass.getField(idFieldName);
            //Logger.getLogger(this.getClass().getName()).log(Level.INFO, "setObjValueFromResultSet(idField={0}, entity={1}, idFieldType={2}", new Object[]{idField, entity.toString(), idFieldType});
            setObjValueFromResultSet(idField, entity, keys, 1, idFieldType);
          } catch (NoSuchFieldException ex) {
            Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
          } catch (SecurityException ex) {
            Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
          }
        }
      }
    } finally {
      close(ps);
    }
    return entity;
  }

  public void setPreparedStatementFromObject(String fieldName, Class type, Object value, PreparedStatement ps, int i) throws SQLException {
    try {
      //Logger.getLogger(this.getClass().getName()).info("fieldName=" + fieldName + ", type=" + type + ", value=" + value + ", preparedStatement=" + ps.toString() +  ", i=" + i);
      if (type.equals(Integer.class) || type.equals(int.class)) {
        ps.setInt(i, ((Integer) value).intValue());
      } else if (type.equals(String.class)) {
        ps.setString(i, String.valueOf(value));
      } else if (type.equals(Double.class) || type.equals(double.class)) {
        ps.setDouble(i, ((Double) value).doubleValue());
      } else if (type.equals(Date.class)) {
        ps.setDate(i, (Date) value);
      } else if (type.equals(java.util.Date.class)) {
        Date date = new Date(((java.util.Date) value).getTime());
        ps.setDate(i, date);
      } else if (type.equals(Timestamp.class)) {
        ps.setTimestamp(i, (Timestamp) value);
      } else {
        Logger.getLogger(this.getClass().getName()).log(Level.INFO, "Error unknown type fieldName={0}, type={1}, value={2}, preparedStatement={3}, i={4}",
                new Object[]{fieldName, type, value, ps.toString(), i});
      }
    } catch (IllegalArgumentException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SecurityException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  public void setPreparedStatementFromEntityField(String fieldName, Class type, T entity, PreparedStatement ps, int i) throws SQLException {
    try {
      //Logger.getLogger(this.getClass().getName()).info("fieldName=" + fieldName + ", type=" + type + ", entity=" + entity + ", preparedStatement=" + ps.toString() +  ", i=" + i);
      Field field = tclass.getField(fieldName);
      Object value = field.get(entity);
      setPreparedStatementFromObject(fieldName, type, value, ps, i);
    } catch (IllegalAccessException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (NoSuchFieldException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
      Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, "SQL exception for query = " + ps.toString(), ex);
      throw ex;
    }
  }


  public boolean updateWhenHavingIdField(Connection conn, T entity) throws SQLException {
    StringBuilder query = new StringBuilder();
    query.append("update ").append(tableName).append(" set ");
    boolean isFirst = true;
    for (Map.Entry<String, Class> fieldToType : nonIdFieldsToType.entrySet()) {
      if (!isFirst) {
        query.append(", ");
      } else {
        isFirst = false;
      }
      query.append(fieldToType.getKey()).append(" = ?");
    }
    query.append(" where ").append(idFieldName).append(" = ?");

    PreparedStatement ps = conn.prepareStatement(query.toString());
    boolean result = false;
    try {
      int i = 1;
      for (Map.Entry<String, Class> fieldToType : nonIdFieldsToType.entrySet()) {
        setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
      }
      setPreparedStatementFromEntityField(idFieldName, idFieldType, entity, ps, i++);

      //Logger.getLogger(this.getClass().getName()).info("ps = " + ps.toString());

      result = ps.execute();
    } finally {
      close(ps);
    }
    return result;
  }
  
  public boolean updateWhenHavingCompositeKey(Connection conn, T entity) throws SQLException {
    StringBuilder query = new StringBuilder();
    query.append("update ").append(tableName).append(" set ");
    boolean isFirst = true;
    for (Map.Entry<String, Class> fieldToType : allFieldsToType.entrySet()) {
      // skip setting elements from composite key
      if (compositeKeyFieldsToType.containsKey(fieldToType.getKey())) {
        continue;
      }
      
      if (!isFirst) {
        query.append(", ");
      } else {
        isFirst = false;
      }
      query.append(fieldToType.getKey()).append(" = ?");
    }
    query.append(" where ");
    isFirst = true;
    for (Map.Entry<String, Class> fieldToType : compositeKeyFieldsToType.entrySet()) {
      if (!isFirst) {
        query.append(", ");
      } else {
        isFirst = false;
      }
      query.append(fieldToType.getKey()).append(" = ?");
    }    
            
    PreparedStatement ps = conn.prepareStatement(query.toString());
    boolean result = false;
    try {
      int i = 1;
      for (Map.Entry<String, Class> fieldToType : allFieldsToType.entrySet()) {
        if (compositeKeyFieldsToType.containsKey(fieldToType.getKey())) {
          continue;
        }
        setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
      }
      for (Map.Entry<String, Class> fieldToType : compositeKeyFieldsToType.entrySet()) {
        setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
      }
      //Logger.getLogger(this.getClass().getName()).info("ps = " + ps.toString());

      result = ps.execute();
    } finally {
      close(ps);
    }
    return result;
  }


  @Override
  public boolean update(Connection conn, T entity) throws SQLException {
    if (idFieldName != null) {
      return updateWhenHavingIdField(conn, entity);
    } else if (compositeKeyFieldsToType.size() > 0) {
      return updateWhenHavingCompositeKey(conn, entity);
    }
    throw new RuntimeException("Cannot update entities where id field is not set up!");
  }

  @Override
  public boolean deleteByKey(Connection conn, Object key) throws SQLException {
    if (idFieldName == null) {
      throw new RuntimeException("Table " + tableName + " doesn't have ID field");
    }
    boolean result = false;
    PreparedStatement ps = conn.prepareStatement("delete from " + tableName + " where " + idFieldName + " = ?");
    try {
      setPreparedStatementFromObject(idFieldName, idFieldType, key, ps, 1);
      //Logger.getLogger(this.getClass().getName()).log(Level.INFO, "ps = {0}", ps.toString());
      result = ps.execute();
    } finally {
      close(ps);
    }
    return result;
  }


  @Override
  public boolean delete(Connection conn, T entity) throws SQLException {
    PreparedStatement ps = null;
    boolean result = false;
    if (idFieldName != null) {
      try {
        ps = conn.prepareStatement("delete from " + tableName + " where " + idFieldName + " = ?");
        setPreparedStatementFromEntityField(idFieldName, idFieldType, entity, ps, 1);
        //Logger.getLogger(this.getClass().getName()).log(Level.INFO, "ps = {0}", ps.toString());
        result =  ps.execute();
      } finally { 
        close(ps);
      }
      return result;
    } else if (compositeKeyFieldsToType.size() > 0) {
      // delete the one with all fields equal
      StringBuilder query = new StringBuilder();
      query.append("delete from ").append(tableName).append(" where ");
      boolean isFirst = true;
      for (Map.Entry<String, Class> fieldToType : compositeKeyFieldsToType.entrySet())  {
        if (!isFirst)  {
          query.append(" and ");
        } else {
          isFirst = false;
        }
        query.append(" ").append(fieldToType.getKey()).append(" = ?");
      }
      try {
        ps = conn.prepareStatement(query.toString());
        int i = 1;
        for (Map.Entry<String, Class> fieldToType : compositeKeyFieldsToType.entrySet()) {
          setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
        }
        result = ps.execute();
      } finally {
        close(ps);
      }
      return result;
    } else {
      // delete the one with all fields equal, no simple key neither composite key
      StringBuilder query = new StringBuilder();
      query.append("delete from ").append(tableName).append(" where ");
      boolean isFirst = true;
      for (Map.Entry<String, Class> fieldToType : nonIdFieldsToType.entrySet())  {
        if (!isFirst)  {
          query.append(" and ");
        } else {
          isFirst = false;
        }
        query.append(" ").append(fieldToType.getKey()).append(" = ?");
      }
      try {
        ps = conn.prepareStatement(query.toString());
        int i = 1;
        for (Map.Entry<String, Class> fieldToType : nonIdFieldsToType.entrySet()) {
          setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
        }
        result = ps.execute();
        } finally {
         close(ps);
      }
      return result;
    }
  }

  @Override
  public List<T> read(String where, Object... params) throws SQLException {
    Connection conn = daoProperties.createConnection();
    List<T> result = new ArrayList<T>();
    try {
      result = read(conn, where, params);
    } finally {
      close(conn);
    }
    return result;
  }
  
  public static void close(PreparedStatement ps) throws SQLException {
    if (ps != null) {
      ps.close();
    }
  }
  
  public static void close(Statement ps) throws SQLException {
    if (ps != null) {
      ps.close();
    }
  }
  
  public static void close(Connection conn) throws SQLException {
    if (conn != null) {
      conn.close();
    }
  }

  @Override
  public T readByKey(Object key) throws SQLException {
    Connection conn = daoProperties.createConnection();
    T result = null;
    try {
      result = readByKey(conn, key);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public List<T> readAll() throws SQLException {
    Connection conn = daoProperties.createConnection();
    List<T> result = new ArrayList<T>();
    try {
      result = readAll(conn);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public T create(T entity) throws SQLException {
    Connection conn = daoProperties.createConnection();
    T result = null;
    try {
    result = create(conn, entity);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public boolean update(T entity) throws SQLException {
    Connection conn = daoProperties.createConnection();
    boolean result = false; 
    try {
      result = update(conn, entity);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public boolean delete(T entity) throws SQLException {
    Connection conn = daoProperties.createConnection();
    boolean result = false; 
    try {
      result = delete(conn, entity);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public boolean deleteByKey(Object key) throws SQLException {
    Connection conn = daoProperties.createConnection();
    boolean result = false; 
    try {
      result = deleteByKey(conn, key);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public <K> Map<K, T> readAllAsMap() throws SQLException {
    HashMap<K, T> result = new HashMap<K, T>();
    for (T elem : readAll()) {
      result.put((K) getIdFromElem(elem), elem);
    }
    return result;
  }

  @Override
  public boolean create(Connection conn, Collection<T> entityCol) throws SQLException {
    String query = getQueryForCreate();

    boolean result = false;
    PreparedStatement ps = conn.prepareStatement(query.toString());
    try {
      for (T entity : entityCol) {
        int i = 1;
        if (idFieldName != null && !idFieldName.equals("") && !autoGeneratedId) {
          setPreparedStatementFromEntityField(idFieldName, idFieldType, entity, ps, i++);
        }
        for (Map.Entry<String, Class> fieldToType : nonIdFieldsToType.entrySet()) {
          setPreparedStatementFromEntityField(fieldToType.getKey(), fieldToType.getValue(), entity, ps, i++);
        }
        result |= ps.execute();
      }
    } finally {
      close(ps);
    }
    return result;
  }

  @Override
  public int delete(Connection conn, String where, Object... params) throws SQLException {
    StringBuilder query = new StringBuilder();
    query.append("delete ");
    query.append(" from ").append(tableName);
    addWhereFieldToQuery(query, where);
    PreparedStatement ps = conn.prepareStatement(query.toString());
    int result;
    try {
      addParamsToQuery(ps, params);
      result = ps.executeUpdate();
    } finally {
      close(ps);
    }
    return result;
  }

  @Override
  public int delete(String where, Object... params) throws SQLException {
    Connection conn = daoProperties.createConnection();
    int result = 0;
    try {
      result = delete(conn, where, params);
    } finally {
      close(conn);
    }
    return result;
  }

  @Override
  public Cursor<T> cursor(Connection conn, String where, Object... params) throws SQLException {
    return cursor(conn, false, where, params);
  }
  
  public Cursor<T> cursor(String where, Object... params) throws SQLException {
    Connection conn = daoProperties.createConnection();
    return cursor(conn, true, where, params);
  }
  
  private Cursor<T> cursor(final Connection conn, final boolean closeConnection, String where, Object... params) throws SQLException {
    StringBuilder query = getQueryBeforeWhere();
    addWhereFieldToQuery(query, where);
    final PreparedStatement ps = conn.prepareStatement(query.toString());
    final ResultSet rs = ps.executeQuery();
    Cursor<T> cursor = new Cursor<T>() {
  
      public boolean hasNext()  {
        try {
          boolean result =  !rs.isLast();
          return result;
        } catch (SQLException ex) {
          Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
      }

      public T next() {
        try {
          if (rs.next()) {
            return readElementFromResultSet(rs);
          }
          return null;
        } catch (SQLException ex) {
          Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
      }

      public void remove() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
      }
      
      public void close() {
        try {
          //StandardDao.close(rs);
          StandardDao.close(ps);
        } catch (SQLException ex) {
          Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
          if (closeConnection) {
            try {
              StandardDao.close(conn);
            } catch (SQLException ex) {
              Logger.getLogger(StandardDao.class.getName()).log(Level.SEVERE, null, ex);
            }
          }
        }
      }
    };
    return cursor;
  }

}
